if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFdsGenProtocolFolderNumbers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	BEGIN
		PRINT 'Dropping Procedure spFdsGenProtocolFolderNumbers'
		drop procedure [dbo].[spFdsGenProtocolFolderNumbers]
	END
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/***************************************************************************
* Name:			spFdsGenProtocolFolderNumbers
* Purpose:		This procedure fetchs the protocols from REX that have folder numbers that
*			match the protocol ID. This rule allows us to assign valid Rex folder numbers to 
*			records that came from the outbound feed in Insight Asset. Only newly inserted 
*			protocols that came from the outbound feed will have this characteristic where
*			the folder number and the Asset Protocol Id are the same. This procedure leverages
*			on existing Rex procedures and tables that assign new Protocol folder numbers
*
* PARAMETERS
* Name			Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
*  @_exitStatus		An exit status of either true or false
***************************************************************************/
CREATE PROCEDURE dbo.spFdsGenProtocolFolderNumbers
AS
BEGIN
	DECLARE	@EXIT_FAILURE			BIT
	DECLARE	@EXIT_SUCCESS			BIT
	
	SET	@EXIT_FAILURE 			= 0
	SET	@EXIT_SUCCESS			= 1


	SET NOCOUNT ON
	--- if #ProtocolsIds table  exists delete it
	
	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '#ProtocolIds')
		DROP TABLE #ProtocolIds

	-- fetch the protocols from REX that have folder numbers that
	-- match the protocol ID.  this is a rule that allows us to
	-- indentify new protocols just added.

	SELECT PrtProtocolId 
	INTO #ProtocolIds
	FROM tblIRBProtocol
	WHERE PrtProtocolId is not null
 	  AND sFolderNumber = cast(PrtProtocolId as varchar(12))
	
	BEGIN TRANSACTION spFdsGenProtocolFolderNumbers
	
	DECLARE @_folderNumber VARCHAR(12)

	DECLARE curIncompleteProtocols CURSOR LOCAL FAST_FORWARD FOR 
	SELECT PrtProtocolId FROM #protocolIds
	
	-- iterate over each protocol and insert the next folder number
	DECLARE @_protocolId	INTEGER
	OPEN curIncompleteProtocols
	
		-- fetch the next protocol ID
		FETCH NEXT FROM curIncompleteProtocols INTO @_protocolId

		WHILE @@FETCH_STATUS = 0
		BEGIN

			-- get the next unformatted folder number
			EXEC spIRBProtocolKeyGetNext  sProtocolNumber

			-- format the folder number and update the IRB protocol
			
			SELECT @_folderNumber = SUBSTRING(sKeyValue ,1,4) + 'P' 
				+ SUBSTRING(sKeyValue ,5, 10) FROM  tblIRBProtocolKey			
	
			UPDATE tblIRBProtocol
				 SET sFolderNumber = @_folderNumber
				 WHERE PrtProtocolId = CAST(@_protocolId AS VARCHAR(10))
			
			
			-- fetch the next protocol ID
			FETCH NEXT FROM curIncompleteProtocols INTO @_protocolId
			
		END

	CLOSE curIncompleteProtocols
	DEALLOCATE curIncompleteProtocols
	
	SET NOCOUNT OFF
	
	COMMIT TRANSACTION spFdsGenProtocolFolderNumbers
	RETURN @EXIT_SUCCESS

	CRITICAL_ERROR_HANDLER:
	BEGIN
		SET NOCOUNT OFF
		ROLLBACK TRANSACTION spFdsGenProtocolFolderNumbers
		RETURN @EXIT_FAILURE
	END

END